﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using HRMSys.Model;
using System.Data;
using System.Transactions;

namespace HRMSys.DAL
{
    public class SalarySheetDAL
    {
        /// <summary>
        /// 查询某张工资单是否已经存在
        /// </summary>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool IsExists(int year, int month, Guid departId) 
        {
            string sql = "select count(*) from T_SalarySheet where Year=@Year and Month=@Month and DepartmentId=@DepartmentId";
            SqlParameter yearParam = new SqlParameter("@Year", year);
            SqlParameter monthParam = new SqlParameter("@Month", month);
            SqlParameter idParam = new SqlParameter("@DepartmentId", departId);
            object obj = SqlHelper.ExecuteScalar(sql, yearParam, monthParam, idParam);
            int result = Convert.ToInt32(obj);

            if (result < 0) 
            {
                throw new Exception("查询结果小于0 , 数据库异常!");
            }
            else if (result > 1)
            {
                throw new Exception("查询结果大于1 , 工资单数据有重复!");
            }
            else
            {
                return result == 1;
            }
        }

        /// <summary>
        /// 根据Id删除工资单
        /// </summary>
        /// <param name="sheetId">工资单ID</param>
        /// <returns></returns>
        public void Delete(int year, int month, Guid departId) 
        {
            //删除工资单 , 应该先删除该工资单的明细数据
            //TODO: 软删除 ?
            string searchSQL = @"select Id from T_SalarySheet 
                                where Year=@Year and Month=@Month and DepartmentId=@DepartmentId";
            SqlParameter yearParam = new SqlParameter("@Year", year);
            SqlParameter monthParam = new SqlParameter("@Month", month);
            SqlParameter departIdParam = new SqlParameter("@DepartmentId", departId);
            Guid sheetId = (Guid)SqlHelper.ExecuteScalar(searchSQL, yearParam, monthParam, departIdParam);
            
            //先删除明细
            string delItemSQL = "delete from T_SalarySheetItem where SheetId=@SheetId";
            SqlParameter sheetIdParam = new SqlParameter("@SheetId", sheetId);
            SqlHelper.ExecuteNonQuery(delItemSQL, sheetIdParam);

            //再删除工资单数据
            string delSheetSQL = "delete from T_SalarySheet where Id=@Id";
            SqlParameter idParam = new SqlParameter("@Id", sheetId);
            SqlHelper.ExecuteNonQuery(delSheetSQL, idParam);
        }

        public void Build(int year, int month, Guid departId)
        {
            //开始生成工资单
            Guid sheetId = Guid.NewGuid();
            string insertSalarySheetSQL = @"insert into T_SalarySheet(Id,Year,Month,DepartmentId) 
                            values(@Id,@Year,@Month,@DepartmentId)";
            SqlParameter idParam = new SqlParameter("@Id", sheetId);
            SqlParameter yearParam = new SqlParameter("@Year", year);
            SqlParameter monthParam = new SqlParameter("@Month", month);
            SqlParameter departIdParam = new SqlParameter("@DepartmentId", departId);

            using (TransactionScope ts = new TransactionScope())
            {
                int sheetResult = SqlHelper.ExecuteNonQuery(insertSalarySheetSQL, idParam, yearParam,
                    monthParam, departIdParam);
                if (sheetResult <= 0)
                {
                    throw new Exception("影响行数<=0, 插入工资单失败");
                }
                else if (sheetResult > 1)
                {
                    throw new Exception("影响行数>1");
                }
                //开始为选择的部门的每一位员工生成工资单明细数据
                else
                {
                    EmployeeDAL employeeDAL = new EmployeeDAL();
                    Employee[] employees = employeeDAL.GetByDepartment(departId);
                    foreach (Employee employee in employees)
                    {
                        string insertSheetItemSQL = @"insert into T_SalarySheetItem(Id,SheetId,EmployeeId,BaseSalary,Bonus,Fine,Other) 
                                                values(newid(),@SheetId,@EmployeeId,@BaseSalary,0,0,0)";
                        SqlParameter sheetIdParam = new SqlParameter("@SheetId", sheetId);
                        SqlParameter employeeIdParam = new SqlParameter("@EmployeeId", employee.Id);
                        SqlParameter baseSalaryParam = new SqlParameter("@BaseSalary", employee.BaseSalary);

                        SqlHelper.ExecuteNonQuery(insertSheetItemSQL, sheetIdParam, employeeIdParam, baseSalaryParam);
                    }
                }
                ts.Complete();//提交事务
            }
        }

        /// <summary>
        /// 获取工资单明细
        /// </summary>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <param name="departId"></param>
        /// <returns></returns>
        public SalarySheetItem[] GetSalarySheetItems(int year, int month, Guid departId) 
        {
            string sql = @"select * from T_SalarySheetItem where SheetId=
                        (select Id from T_SalarySheet 
                            where Year=@Year and Month=@Month and DepartmentId=@DepartmentId)";
                        //where Year=@Year and Month=@Month and DepartmentId=@DepartmentId";
            SqlParameter yearParam = new SqlParameter("@Year",year);
            SqlParameter monthParam = new SqlParameter("@Month",month);
            SqlParameter departIdParam = new SqlParameter("@DepartmentId",departId);
            DataTable table = SqlHelper.ExecuteDataTable(sql, yearParam, monthParam, departIdParam);
            int rowCount = table.Rows.Count;
            SalarySheetItem[] items = new SalarySheetItem[rowCount];
            for (int i = 0; i < rowCount; i++)
            {
                DataRow row = table.Rows[i];
                SalarySheetItem item = new SalarySheetItem();
                item.BaseSalary = (decimal)row["BaseSalary"];
                item.Bonus = (decimal)row["Bonus"];
                item.EmployeeId = (Guid)row["EmployeeId"];
                item.Fine = (decimal)row["Fine"];
                item.Id = (Guid)row["Id"];
                item.Other = (decimal)row["Other"];
                item.SheetId = (Guid)row["SheetId"];

                items[i] = item;
            }
            return items;
        }

        /// <summary>
        /// 更新工资单明细
        /// </summary>
        /// <param name="item"></param>
        public void Update(SalarySheetItem item) 
        {
            string sql = @"update T_SalarySheetItem set BaseSalary=@BaseSalary,Bonus=@Bonus,Fine=@Fine,Other=@Other
                            where Id=@Id";
            SqlParameter baseSalaryParam = new SqlParameter("@BaseSalary", item.BaseSalary);
            SqlParameter bonusParam = new SqlParameter("@Bonus", item.Bonus);
            SqlParameter fineParam = new SqlParameter("@Fine", item.Fine);
            SqlParameter otherParam = new SqlParameter("@Other", item.Other);
            SqlParameter idParam = new SqlParameter("@Id", item.Id);

            SqlHelper.ExecuteNonQuery(sql, baseSalaryParam, bonusParam, fineParam, otherParam, idParam);
        }
    }
}
